﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataClass
{
    public class DataConnect : IDisposable
    {
        #region IDisposable Members

        public void Dispose()
        {
            _dbConn = null;
            _dbTrans = null;
        }

        #endregion

        #region Property

        SqlConnection _dbConn;

        public SqlConnection DbConn
        {
            get { return _dbConn; }
            set { _dbConn = value; }
        }

        SqlTransaction _dbTrans;

        #endregion

        #region Constructor

        public DataConnect()
        {
            string _strConn = ConfigurationManager.AppSettings["connectionstring"].ToString();
            _dbConn = new SqlConnection(_strConn);
        }

        #endregion

        #region Connect, Transaction

        public void Open()
        {
            if(_dbConn.State != ConnectionState.Open)
                _dbConn.Open();
        }

        public void Close()
        {
            if (_dbConn.State != ConnectionState.Closed)
                _dbConn.Close();
        }

        public SqlTransaction BeginTransaction()
        {
            _dbTrans = _dbConn.BeginTransaction();
            return _dbTrans;
        }

        public void CommitTransaction()
        {
            _dbTrans.Commit();
        }

        public void RollbackTransaction()
        {
            _dbTrans.Rollback();
        }

        #endregion

        #region Methods to DB

        public void SaveData(string storeName, string[] strParamName, object[] objParamValue)
        {
            try
            {
                SqlCommand comd = new SqlCommand(storeName, _dbConn, _dbTrans);
                comd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < strParamName.Count(); i++)
                {
                    comd.Parameters.AddWithValue(strParamName[i], objParamValue[i]);
                }
                comd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataSet GetData(string storeName, string[] strParamName, object[] objParamValue)
        {
            try
            {
                SqlCommand comd = new SqlCommand(storeName, _dbConn);
                comd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < strParamName.Count(); i++)
                {
                    comd.Parameters.AddWithValue(strParamName[i], objParamValue[i]);
                }

                SqlDataAdapter da = new SqlDataAdapter(comd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public int DeleteData(string storeName, string[] strParamName, object[] objParamValue)
        {
            int num;
            try
            {
                SqlCommand comd = new SqlCommand(storeName, _dbConn, _dbTrans);
                comd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < strParamName.Count(); i++)
                {
                    comd.Parameters.AddWithValue(strParamName[i], objParamValue[i]);
                }

                num = comd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return num;
        }

        public bool CheckConnection()
        {
            bool result = false;

            //try
            //{
                _dbConn.Open();

                if (_dbConn.State == ConnectionState.Open)
                    result = true;
                else
                    result = false;

            //}
            //catch
            //{
            //    result = false;
            //}
            return result;
        }

        #endregion
    }
}
